# 解析excel文件
import pandas as pd


def add_hit(index, filed, filed_hit_dict):
    hit_arr = filed_hit_dict.get(index)
    if hit_arr:
        if filed in hit_arr:
            hit_arr[filed] = hit_arr[filed] + 1
        else:
            hit_arr[filed] = 1
    else:
        filed_hit_dict[index] = {filed: 1}


def build_hit(columns, headers_dict):
    filed_hit_dict = {
        # index : {field1 : 1, field2: 2}
    }
    # 找到对应这几个字段对应的具体下标
    for column in columns:
        for header in headers_dict:
            header_arr = headers_dict[header]
            for header_des in header_arr:
                # 加命中次数
                if header_des.__contains__(column) or column.__contains__(header_des):
                    add_hit(columns.index(column), header, filed_hit_dict)
                    print(column, columns.index(column), header)
    return filed_hit_dict


def index_with_properties(filed_hit_dict):
    result_dict = {}
    for key in filed_hit_dict:
        hit_dict = filed_hit_dict[key]
        if hit_dict.__len__ == 1:
            result_dict[key] = list(hit_dict)[0]
        else:
            # 获取最大value的
            max_key = ''
            max_num = 0
            for field in hit_dict:
                num = hit_dict[field]
                if num > max_num:
                    max_key = field
                    max_num = num
            result_dict[key] = max_key

    return result_dict


def parse(parse_id, parse_url):
    # path = parse_url
    path = r'E:/files/3677可用.xlsx'
    sheets = pd.read_excel(path, keep_default_na=False, dtype={'upass': str}, sheet_name=None)
    total = 0
    # TODO 释放资源
    for sheet_name in sheets:
        data = sheets[sheet_name]
        columns = data.columns.values.tolist()
        # 定义默认表头信息 名称，规格，单位，条码，零售价
        # TODO 从数据库中进行获取
        headers_dict = {
            'name': ['名称', '商品名称', '商品名'],
            'specification': ['规格', '商品规格', '商品特征'],
            'category': ['类型', '商品类型'],
            'unit': ['单位'],
            'code': ['条码', '条形码'],
            'price': ['价格', '零售价', '零售价格', '卖价', '定价', '市场定价']
        }
        filed_hit_dict = build_hit(columns, headers_dict)
        if not filed_hit_dict:
            print('此表无可用信息')
            continue
        # 获取属性对象 key:index value:filedName
        properties_dict = index_with_properties(filed_hit_dict)
        # 遍历对象 取到对应的值 并且封装到对象中
        datas = data.values.tolist()
        data_arr = []
        for item in datas:
            total += 1
            data_object = {}
            for properties_index in properties_dict:
                data_object[properties_dict[properties_index]] = item[properties_index]
            print(data_object)
            data_arr.append(data_object)
        # 同步每次提交100条数据到服务
    return dict(code=200, data=dict(total=total, parse_id=parse_id))
